package com.yingside.jdbc.crm;

import com.yingside.jdbc.Teacher;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDao {
    public List<Employee> list(){
        Connection conn = null;
        Statement stmt = null;
        //结果集(游标)
        ResultSet rs = null;


        List<Employee> list = new ArrayList<Employee>();

        try {
            //3.获取连接
            conn = DBHelper.getConn();

            //4.获取stmt对象
            stmt = conn.createStatement();

            //5.执行sql语句
            String sql = "select t_emp.id,t_emp.emp_name,t_emp.emp_tel,t_dept.dept_name " +
                    " from t_emp,t_dept" +
                    " where t_dept.id=t_emp.fk_deptid";

            rs = stmt.executeQuery(sql);

            while(rs.next()){

                Employee employee = new Employee();
                employee.setId(rs.getInt("id"));
                employee.setEmpName(rs.getString("emp_name"));
                employee.setEmpTel(rs.getString("emp_tel"));

                Dept dept = new Dept();
                dept.setDeptName(rs.getString("dept_name"));
                employee.setDept(dept);
                list.add(employee);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.close(rs,stmt,conn);
        }

        return list;
    }

    /**
     * 根据主键id查询返回Employee对象
     * @param id 主键id
     * @return Employee对象，如果没有数据，返回null
     */
    public Employee get(int id){
        Connection conn = null;
        Statement stmt = null;
        //结果集(游标)
        ResultSet rs = null;


        Employee employee = null;

        try {
            //3.获取连接
            conn = DBHelper.getConn();

            //4.获取stmt对象
            stmt = conn.createStatement();

            //5.执行sql语句
            String sql = "select t_emp.id,t_emp.emp_sex,t_emp.emp_name,t_emp.emp_tel,t_dept.id as deptid,t_dept.dept_name " +
                    " from t_emp,t_dept" +
                    " where t_dept.id=t_emp.fk_deptid" +
                    " and t_emp.id=" + id;

            rs = stmt.executeQuery(sql);

            if(rs.next()){

                employee = new Employee();
                employee.setId(rs.getInt("id"));
                employee.setEmpName(rs.getString("emp_name"));
                employee.setEmpTel(rs.getString("emp_tel"));
                employee.setEmpSex(rs.getString("emp_sex"));

                Dept dept = new Dept();
                dept.setId(rs.getInt("deptid"));
                dept.setDeptName(rs.getString("dept_name"));
                employee.setDept(dept);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.close(rs,stmt,conn);
        }

        return employee;
    }

    /**
     * 根据员工信息模糊查询
     * @param empName 员工姓名
     * @return 返回模糊查询的集合
     */
    public List<Employee> queryLikeEmpName(String empName){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        List<Employee> list = new ArrayList<Employee>();

        try {
            //3.获取连接
            conn = DBHelper.getConn();

            //sql语句
            String sql = "select t_emp.id,t_emp.emp_sex,t_emp.emp_name,t_emp.emp_tel,t_dept.id as deptid,t_dept.dept_name " +
                    " from t_emp,t_dept " +
                    " where t_dept.id=t_emp.fk_deptid " +
                    " and t_emp.emp_name like ?";

            //4.获取stmt对象
            ps = conn.prepareStatement(sql);
            ps.setString(1,"%" + empName + "%");

            rs = ps.executeQuery();

            while(rs.next()){
                Employee employee = new Employee();
                employee.setId(rs.getInt("id"));
                employee.setEmpName(rs.getString("emp_name"));
                employee.setEmpTel(rs.getString("emp_tel"));
                employee.setEmpSex(rs.getString("emp_sex"));
                Dept dept = new Dept();
                dept.setId(rs.getInt("deptid"));
                dept.setDeptName(rs.getString("dept_name"));
                employee.setDept(dept);
                list.add(employee);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.close(rs,ps,conn);
        }

        return list;
    }

    public int add(Employee employee){
        int n = 0;
        Connection conn = null;
        Statement stmt = null;

        try {
            //3.获取连接
            conn = DBHelper.getConn();

            //4.获取stmt对象
            stmt = conn.createStatement();

            //5.执行sql语句
            String sql = "insert into t_emp(emp_name,emp_tel,emp_sex,fk_deptid)" +
                    " values('"+employee.getEmpName()+"','"+employee.getEmpTel()+"','"+employee.getEmpSex()+"',"+employee.getDept().getId()+")";

            n = stmt.executeUpdate(sql);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.close(null,stmt,conn);
        }

        return n;
    }

    public int update(Employee employee){
        int n = 0;
        Connection conn = null;
        //使用预编译语句
        PreparedStatement ps = null;

        try {
            //3.获取连接
            conn = DBHelper.getConn();

            String sql = "update t_emp set emp_name=?,emp_tel=?,emp_sex=?,fk_deptid=?" +
                    " where t_emp.id=?";

            //4.获取PreparedStatement对象
            //使用PreparedStatement好处
            //1.可以不用再拼接字符串了，直接用？代替需要传入的值，在后面直接用方法替换掉？就可以了
            //2.极大的提高运行效率,sql语句如果有语法错误，直接返回错误，不需要再到数据库里面执行
            //3.防止一些简单的数据库注入
            ps = conn.prepareStatement(sql);

            //替换?,按照？的顺序从1开始替换
            ps.setString(1,employee.getEmpName());
            ps.setString(2,employee.getEmpTel());
            ps.setString(3,employee.getEmpSex());
            ps.setInt(4,employee.getDept().getId());
            ps.setInt(5,employee.getId());

            //注意：这里直接使用executeUpdate()方法，里面是没有参数的
            //因为如果有参数，相当于又调用了父类Statement里面的方法
            n = ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.close(null,ps,conn);
        }

        return n;
    }

    public static void main(String[] args) {
        EmployeeDao employeeDao = new EmployeeDao();
        List<Employee> list = employeeDao.list();
        for (Employee emp:list) {
            System.out.println("emp = " + emp);
        }
    }

    /**
     * 根据主键id删除数据
     * @param key 主键id
     * @return 删除成功返回true，否则false
     */
    public boolean delete(int key) {
        int n = 0;
        Connection conn = null;
        //使用预编译语句
        PreparedStatement ps = null;

        boolean flag = false;

        try {
            //3.获取连接
            conn = DBHelper.getConn();

            String sql = "delete from t_emp where t_emp.id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,key);

            n = ps.executeUpdate();

            if(n > 0) flag = true;

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.close(null,ps,conn);
        }

        return flag;
    }
}
